package com.shujia.spark.sql

import org.apache.spark.sql.{DataFrame, SparkSession}

object Demo8Stu {
  def main(args: Array[String]): Unit = {

    val spark: SparkSession = SparkSession.builder()
      .master("local")
      .appName("stu")
      .getOrCreate()

    val stu: DataFrame = spark
      .read
      .format("csv")
      .option("sep", ",")
      .schema("name STRING,item STRING,score DOUBLE")
      .load("data/stu.txt")

    stu.createOrReplaceTempView("stu")

    /**
      *
      * 1、行列转换
      * *
      * 表1
      * 姓名,科目,分数
      * name,item,score
      * 张三,数学,33
      * 张三,英语,77
      * 李四,数学,66
      * 李四,英语,78
      * *
      *
      * 表2
      * 姓名,数学,英语
      * name,math,english
      * 张三,33,77
      * 李四,66,78
      * *
      * 1、将表1转化成表2
      * 2、将表2转化成表1
      *
      *
      */


    val stu1: DataFrame = spark.sql(
      """
        |select
        |name,
        |sum(case when item = '数学' then score else 0 end) as math,
        |sum(case when item = '英语' then score else 0 end) as english
        |from stu
        |group by name
        |
      """.stripMargin)

    stu1.show()


    stu1.createOrReplaceTempView("stu1")

    spark.sql(
      """
        |
        |select name,explode(map('数学',math,'英语',english)) as (item,score)  from stu1
        |
      """.stripMargin).show()


  }

}
